﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using Home.Utils;
using NhanAi.Entity;
using NhanAi.App_Code;
namespace NhanAi.DAO
{
    public class PostDao : DBConnection
    {
        public int Count()
        {
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmPost",conn);
            if (Connect())
            {
                int count = 0;
                count=(int)cmd.ExecuteScalar()
;
                Disconnect();
                return count;
            }
            return 0;
        }
        public void Create(Post p)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmPost ");
            if (Connect())
            {
               nb.AddValueField("AuthorId", p.AuthorId);
                nb.AddValueField("PostDate", p.PostDate.ToString("MM/dd/yyyy hh:mm:ss"));
                nb.AddValueField("Content", p.Content);
                nb.AddValueField("ContentEn", p.ContentEn);
                nb.AddValueField("Title", p.Title);
                nb.AddValueField("TitleEn", p.TitleEn);
                nb.AddValueField("Excerpt", p.Excerpt);
                nb.AddValueField("ExcerptEn", p.ExcerptEn);
                nb.AddValueField("PostStatus", p.Status);
                nb.AddValueField("ModifiedDate", p.ModifiedDate.ToString("MM/dd/yyyy hh:mm:ss"));
               nb.AddValueField("ParentId", p.ParentId);
                nb.AddValueField("PostGuid", p.Guid);
              nb.AddValueField("PostName", p.Name);
                nb.AddValueField("MenuOrder", p.MenuOrder);
                nb.AddValueField("PostType", p.PostType);
                nb.AddValueField("CommentCount", p.CommentCount);
                nb.AddValueField("Protected", p.Protected);
                nb.AddValueField("PostAttribute", p.PostAttribute);
                nb.AddValueField("ViewCount", p.ViewCount);
                OleDbCommand cmd = nb.GetCreateCommand(conn);
                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select @@IDENTITY";
                    p.Id = (int)cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                Disconnect();
            }

        }
        public void Update(Post p)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmPost");
            if (Connect())
            {

                //nb.AddValueField("ID",u.Id);
                nb.AddValueField("AuthorId", p.AuthorId);
               
                nb.AddValueField("Content", p.Content);
                nb.AddValueField("ContentEn", p.ContentEn);
                nb.AddValueField("Title", p.Title);
                nb.AddValueField("TitleEn", p.TitleEn);
                nb.AddValueField("Excerpt", p.Excerpt);
                nb.AddValueField("ExcerptEn", p.ExcerptEn);
                nb.AddValueField("PostStatus", p.Status);
                nb.AddValueField("ModifiedDate", p.ModifiedDate.ToString());
                nb.AddValueField("ParentId", p.ParentId);
                nb.AddValueField("PostGuid", p.Guid);
                nb.AddValueField("PostName", p.Name);
                nb.AddValueField("MenuOrder", p.MenuOrder);
                nb.AddValueField("PostType", p.PostType);
                nb.AddValueField("CommentCount", p.CommentCount);
                nb.AddValueField("Protected", p.Protected);
                nb.AddValueField("PostAttribute", p.PostAttribute);
                nb.AddConditionField("Id", p.Id);
                OleDbCommand cmd = nb.GetUpdateCommand(conn, true);
                cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        public void UpdateViewCount(int postId, int quantity)
        {
            
            if (Connect())
            {
                try
                {
                    OleDbCommand cmd = new OleDbCommand("update HmPost set ViewCount=ViewCount+@quantity where Id=@id",conn);

                    cmd.Parameters.AddWithValue("@quantity", quantity);
                    cmd.Parameters.AddWithValue("@id", postId);
                    cmd.ExecuteNonQuery();
                    Disconnect();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        public void UpdateCommentCount(int postId, int quantity)
        {

            if (Connect())
            {
                try
                {
                    OleDbCommand cmd = new OleDbCommand("update HmPost set CommentCount=CommentCount+@quantity where Id=@id",conn);

                    cmd.Parameters.AddWithValue("@quantity", quantity);
                    cmd.Parameters.AddWithValue("@id", postId);
                    cmd.ExecuteNonQuery();
                    Disconnect();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        public void Delele(int uid)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmPost");
            if (Connect())
            {

                nb.AddConditionField("ID", uid);
                OleDbCommand cmd = nb.GetDeleteCommand(conn, true);
                cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        public void Delete(string ids)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmPost");
            OleDbCommand cmd = new OleDbCommand("delete from HmPost where Id in (" + ids + ")", conn);
            if (Connect())
            {
                cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        public Post Read(int id, int lang)
        {
            Post t = null;
            if (Connect())
            {
                string commandText = "";
                if (NAConstants.HasFlag(lang, NAConstants.ALL_LANGUAGE))
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.Content, a.ContentEn, a.Title, a.TitleEn, a.Excerpt, a.ExcerptEn, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, b.UserLogin,a.PostAttribute, a.ViewCount, a.PostName from HmPost a left outer join HmUser b  on a.AuthorId=b.Id  where a.Id=@id";
                }
                else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.Content, a.Title, a.Excerpt, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, b.UserLogin,a.PostAttribute, a.ViewCount, a.PostName from HmPost a left outer join HmUser b  on a.AuthorId=b.Id  where a.Id=@id";
                }
                else
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.ContentEn, a.TitleEn, a.ExcerptEn, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, b.UserLogin,a.PostAttribute, a.ViewCount, a.PostName from HmPost a left outer join HmUser b  on a.AuthorId=b.Id  where a.Id=@id";
                }
                OleDbCommand cmd = new OleDbCommand(commandText, conn);
                cmd.Parameters.AddWithValue("@id", id);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    int offset=0;
                    t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.ALL_LANGUAGE))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(5);
                        t.Excerpt = dr.GetString(7);
                 
                        t.ContentEn = dr.GetString(4);
                        t.TitleEn = dr.GetString(6);
                        t.ExcerptEn = dr.GetString(8);
                    }
                    else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(4);
                        t.Excerpt = dr.GetString(5);
                        offset = 3;
                    }
                    else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                        t.ContentEn = dr.GetString(3);
                        t.TitleEn = dr.GetString(4);
                        t.ExcerptEn = dr.GetString(5);
                        offset = 3;
                    }
                    t.Status = dr.GetInt16(9-offset);
                    t.ModifiedDate = dr.GetDateTime(10-offset);
                    t.ParentId = dr.GetInt32(11-offset);
                    t.Guid = dr.GetString(12-offset);
                    t.MenuOrder = dr.GetInt16(13-offset);
                    t.PostType = dr.GetString(14-offset);
                    t.CommentCount = dr.GetInt32(15-offset);
                    t.Protected = dr.GetBoolean(16-offset);
                    t.User = new User();
                    if (!dr.IsDBNull(17-offset))
                    {
                        t.User.UserLogin = dr.GetString(17 - offset);
                    }
                    t.PostAttribute = dr.GetInt16(18 - offset);
                    t.ViewCount = dr.GetInt32(19 - offset);
                    t.Name = dr.GetString(20 - offset);
                }
                Disconnect();
            }
            return t;
        }
        public Post Read(string name,string type, int lang)
        {
            Post t = null;
            if (Connect())
            {
                string commandText = "";
                if (NAConstants.HasFlag(lang, NAConstants.ALL_LANGUAGE))
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.Content, a.ContentEn, a.Title, a.TitleEn, a.PostGuid, a.MenuOrder, a.CommentCount, a.Protected from HmPost a where a.PostName=@name and a.PostStatus=@status";
                }
                else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.Content, a.Title, a.PostGuid, a.MenuOrder, a.CommentCount, a.Protected from HmPost a where a.PostName=@name AND a.PostStatus=@status";
                }
                else
                {
                    commandText = "select a.Id, a.AuthorId,a.PostDate,a.ContentEn, a.TitleEn,a.PostGuid, a.MenuOrder, a.CommentCount, a.Protected from HmPost a where a.PostName=@name AND a.PostStatus=@status";
                }
                OleDbCommand cmd = new OleDbCommand(commandText, conn);
                cmd.Parameters.AddWithValue("@name", name);
               // cmd.Parameters.AddWithValue("@type", type);
                cmd.Parameters.AddWithValue("@status", NAConstants.POST_STA_POST);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    int offset = 0;
                    t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.ALL_LANGUAGE))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(5);                      

                        t.ContentEn = dr.GetString(4);
                        t.TitleEn = dr.GetString(6);
                        offset = 1;
                    }
                    else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(4);                    
                        offset = 3;
                    }
                    else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                        t.ContentEn = dr.GetString(3);
                        t.TitleEn = dr.GetString(4);
                        offset = 3;
                    }
                   t.Guid = dr.GetString(8 - offset);
                    t.MenuOrder = dr.GetInt16(9 - offset);
                    
                    t.CommentCount = dr.GetInt32(10 - offset);
                    t.Protected = dr.GetBoolean(11 - offset);
                   
                }
                Disconnect();
            }
            return t;
        }
        public List<Post> ReadMany(int pageSize, int pageNum, int lang,string type, string text, bool fullSearch,int attribute,int status, DateTime? fromDate, DateTime? toDate,out int total)
        {
            List<Post> list = new List<Post>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmPost where PostType=@type", conn);
            cmd.Parameters.AddWithValue("@type", type);
            total = 0;
            if (Connect())
            {
                 total = (int)cmd.ExecuteScalar();
                string commandText = null;
                if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    commandText = "select TOP #pageSize * from (select TOP #total a.Id, a.AuthorId,a.PostDate,a.Content, a.Title, a.Excerpt, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, b.UserLogin, a.ViewCount, a.PostName from HmPost a left outer join HmUser b  on a.AuthorId=b.Id  where 1=1";
                else
                {
                    commandText = "select TOP #pageSize * from (select TOP #total a.Id, a.AuthorId,a.PostDate,a.ContentEn, a.TitleEn, a.ExcerptEn, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected,b.UserLogin,a.ViewCount, a.PostName from HmPost a left outer join HmUser b  on a.AuthorId=b.Id  where 1=1";
                }
                QueryCommandBuilder qb = new QueryCommandBuilder(commandText);
                qb.AddSuffix("order by a.Id asc) order by Id desc");
                qb.AddParam("pageSize", pageSize);
                 int tmp =total- (pageNum - 1) * pageSize;
                qb.AddParam("total",tmp>0?tmp:1);
                if (!String.IsNullOrEmpty(text))
                {
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        qb.AddWithValue("Title", text, "like", "AND","@Title","(","");
                        qb.AddWithValue("Excerpt", text, "like", "OR","@Excerpt","",")");
                        if (fullSearch) qb.AddWithValue("Content", text, "like", "OR");
                    }
                    else
                    {
                        qb.AddWithValue("TitleEn", text, "like", "AND","@TitleEn","(","");
                        qb.AddWithValue("ExcerptEn", text, "like", "OR","@ExcerptEn","",")");
                        if (fullSearch) qb.AddWithValue("ContentEn", text, "like", "OR");
                    }
                }
                else
                    if (fromDate != null && toDate != null)
                    {
                        qb.AddWithValue("PostDate", fromDate, ">=");
                        qb.AddWithValue("PostDate", toDate, "<=");
                    }
                if (attribute > -1) qb.AddWithValue("PostAttribute",attribute);
                if (!String.IsNullOrEmpty(type)) qb.AddWithValue("PostType",type);
                if (status > -1) qb.AddWithValue("PostStatus", status);
                cmd = qb.GetBuildedCommand(conn);

                OleDbDataReader dr = cmd.ExecuteReader();                
                while (dr.Read())
                {
                    Post t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(4);
                        t.Excerpt = dr.GetString(5);
                        
                    }
                    else if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                        t.ContentEn = dr.GetString(3);
                        t.TitleEn = dr.GetString(4);
                        t.ExcerptEn = dr.GetString(5);
                       
                    }
                    t.Status = dr.GetInt16(6);
                    t.ModifiedDate = dr.GetDateTime(7);
                    t.ParentId = dr.GetInt32(8);
                    t.Guid = dr.GetString(9);
                    t.MenuOrder = dr.GetInt16(10);
                    t.PostType = dr.GetString(11);
                    t.CommentCount = dr.GetInt32(12);
                    t.Protected = dr.GetBoolean(13);                  
                    t.User = new User();
                    if (!dr.IsDBNull(14))
                    {
                        t.User.Nicename = dr.GetString(14);
                    }
                    t.ViewCount = dr.GetInt32(15);
                    t.Name = dr.GetString(16);
                    list.Add(t);
                }
            }
            return list;
        }

        public List<Post> ReadByTaxonomy(int pageSize, int pageNum, int lang, string type,int taxonomyId, string text, bool fullSearch,int attribute,int status, DateTime? fromDate, DateTime? toDate, out int total)
        {
            List<Post> list = new List<Post>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmPost where PostType=@type", conn);
            cmd.Parameters.AddWithValue("@type", type);
            total = 0;
            if (Connect())
            {
                total = (int)cmd.ExecuteScalar();
                string commandText = null;
                if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    commandText = "select TOP #pageSize * from (select TOP #total a.Id, a.AuthorId,a.PostDate,a.Content, a.Title, a.Excerpt, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected,  a.ViewCount, a.PostName from HmPost a inner join HmTaxonomyRelationship b  on a.Id=b.ObjectId where 1=1";
                else
                {
                    commandText = "select TOP #pageSize * from (select TOP #total a.Id, a.AuthorId,a.PostDate,a.ContentEn, a.TitleEn, a.ExcerptEn, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, a.ViewCount, a.PostName from HmPost a  inner join HmTaxonomyRelationship b  on a.Id=b.ObjectId where 1=1";
                }
                QueryCommandBuilder qb = new QueryCommandBuilder(commandText);
                qb.AddSuffix("AND b.TaxonomyId= @taxId order by a.Id asc) order by Id desc");
                qb.AddParam("pageSize", pageSize);
                int tmp=total - (pageNum - 1) * pageSize;
                qb.AddParam("total",tmp>0?tmp:1 );
                if (!String.IsNullOrEmpty(text))
                {
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        qb.AddWithValue("a.Title", text, "like", "OR","Title");
                        qb.AddWithValue("a.Excerpt", text, "like", "OR","Excerpt");
                        if (fullSearch) qb.AddWithValue("a.Content", text, "like", "OR","Content");
                    }
                    else
                    {
                        qb.AddWithValue("TitleEn", text, "like", "OR");
                        qb.AddWithValue("ExcerptEn", text, "like", "OR");
                        if (fullSearch) qb.AddWithValue("ContentEn", text, "like", "OR");
                    }
                }
                else
                    if (fromDate != null && toDate != null)
                    {
                        qb.AddWithValue("PostDate", fromDate, ">=");
                        qb.AddWithValue("PostDate", toDate, "<=");
                    }
                if (attribute > -1) qb.AddWithValue("PostAttribute", attribute);
                if (!String.IsNullOrEmpty(type)) qb.AddWithValue("PostType",type);
                if (status > -1) qb.AddWithValue("PostStatus",status);
                
                cmd = qb.GetBuildedCommand(conn);
                cmd.Parameters.AddWithValue("@taxId", taxonomyId);
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Post t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(4);
                        t.Excerpt = dr.GetString(5);
                        //t.Parent.TermName = dr.GetString(14);
                    }
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                        t.ContentEn = dr.GetString(3);
                        t.TitleEn = dr.GetString(4);
                        t.ExcerptEn = dr.GetString(5);
                     //   t.Parent.TermNameEn = dr.GetString(14);
                    }
                    t.Status = dr.GetInt16(6);
                    t.ModifiedDate = dr.GetDateTime(7);
                    t.ParentId = dr.GetInt32(8);
                    t.Guid = dr.GetString(9);
                    t.MenuOrder = dr.GetInt16(10);
                    t.PostType = dr.GetString(11);
                    t.CommentCount = dr.GetInt32(12);
                    t.Protected = dr.GetBoolean(13);
                    t.ViewCount = dr.GetInt32(14);
                    t.Name = dr.GetString(15);
                    //t.Parent.Id = t.ParentId;
                    
                    list.Add(t);
                }
            }
            return list;
        }
        public List<Post> ReadAdv(int pageSize, int pageNum, int lang, string type, string text, int status, out int total)
        {
            List<Post> list = new List<Post>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmPost where PostType=@type and (Title like @kword or TitleEn like @kword or Excerpt like @kword or ExcerptEn like @kword) and PostStatus < @status", conn);
            cmd.Parameters.AddWithValue("@type", type);
            cmd.Parameters.AddWithValue("@kword", text);
            cmd.Parameters.AddWithValue("@status", status);
           
            total = 0;
            if (Connect())
            {
                total = (int)cmd.ExecuteScalar();
                string commandText = null;
                int tmp = total - (pageNum - 1) * pageSize;
                if (tmp <= 0) tmp = 1;
                if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    commandText = "select TOP "+pageSize+" * from (select TOP "+tmp+" a.Id, a.AuthorId,a.PostDate,a.Content, a.Title, a.Excerpt, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected,  a.ViewCount, a.PostName from HmPost a";
                else
                {
                    commandText = "select TOP "+pageSize+" * from (select TOP "+tmp+" a.Id, a.AuthorId,a.PostDate,a.ContentEn, a.TitleEn, a.ExcerptEn, a.PostStatus, a.ModifiedDate, a.ParentId,a.PostGuid, a.MenuOrder, a.PostType, a.CommentCount, a.Protected, a.ViewCount, a.PostName from HmPost a";
                }
                commandText += " where PostType=@type and PostStatus< @status and (Title like @kword or TitleEn like @kword or Excerpt like @kword or ExcerptEn like @kword) order by Id asc) order by Id desc";
                
                cmd = new OleDbCommand(commandText,conn);
                cmd.Parameters.AddWithValue("@type", type);
                cmd.Parameters.AddWithValue("@status", status);
                cmd.Parameters.AddWithValue("@kword", text);
               
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Post t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {
                        t.Content = dr.GetString(3);
                        t.Title = dr.GetString(4);
                        t.Excerpt = dr.GetString(5);                      
                    }
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                        t.ContentEn = dr.GetString(3);
                        t.TitleEn = dr.GetString(4);
                        t.ExcerptEn = dr.GetString(5);                  
                    }
                    t.Status = dr.GetInt16(6);
                    t.ModifiedDate = dr.GetDateTime(7);
                    t.ParentId = dr.GetInt32(8);
                    t.Guid = dr.GetString(9);
                    t.MenuOrder = dr.GetInt16(10);
                    t.PostType = dr.GetString(11);
                    t.CommentCount = dr.GetInt32(12);
                    t.Protected = dr.GetBoolean(13);
                    t.ViewCount = dr.GetInt32(14);
                    t.Name = dr.GetString(15);
            
                    list.Add(t);
                }
            }
            return list;
        }
        public List<Post> ReadViewMost( int lang,int quantity)
        {
            List<Post> list = new List<Post>();
            OleDbCommand cmd = new OleDbCommand();
            //cmd.Parameters.AddWithValue("@type", type);
            //total = 0;
            if (Connect())
            {
               // total = (int)cmd.ExecuteScalar();
                string commandText = null;
                if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    commandText = "select TOP #total * from (SELECT a.Id, a.AuthorId,a.PostDate, a.Title, a.Excerpt,a.PostGuid, a.CommentCount, a.Protected,  a.ViewCount from HmPost a where a.PostStatus=@status AND PostType=\"post\" order by ViewCount desc)";
                else
                {
                    commandText = "select TOP #total * from (select TOP #total a.Id, a.AuthorId,a.PostDate, a.TitleEn, a.ExcerptEn,a.PostGuid,  a.CommentCount, a.Protected, a.ViewCount from HmPost a where a.PostStatus=@status)";
                }
                QueryCommandBuilder qb = new QueryCommandBuilder(commandText);
               // qb.AddSuffix("");
                qb.AddParam("total", quantity);
                           
                cmd = qb.GetBuildedCommand(conn);
                cmd.Parameters.AddWithValue("status", NhanAi.App_Code.NAConstants.POST_STA_POST);  
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Post t = new Post();
                    t.Id = dr.GetInt32(0);
                    t.AuthorId = dr.GetInt32(1);
                    t.PostDate = dr.GetDateTime(2);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI))
                    {                     
                        t.Title = dr.GetString(3);
                        t.Excerpt = dr.GetString(4);
                      
                    }
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN))
                    {
                       
                        t.TitleEn = dr.GetString(3);
                        t.ExcerptEn = dr.GetString(4);
             
                    }                   
                    t.Guid = dr.GetString(5);                    
                    //t.PostType = dr.GetString(6);
                    t.CommentCount = dr.GetInt32(6);
                    t.Protected = dr.GetBoolean(7);
                    t.ViewCount = dr.GetInt32(8);
                    list.Add(t);
                }
            }
            return list;
        }
    }
}